package com.jdbc.crud;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wzm
 * @create 2023-02-23 11:17
 */
public class StuentCRUD {
    private Connection conn = null; //连接对象
    private PreparedStatement pst = null;//执行sql对象
    private ResultSet rs = null;//结果集 查询

    //增加数据
    @Test
    public void insertStudent(){
        try {
            //1.打开连接
             conn = JDBCUtils.getConn();

            //2.书写sql语句
            String sql = "insert into students values(?,?,?,?)";

            //3.得到执行sql的对象 将sql预编译
             pst = conn.prepareStatement(sql);

            //3.1需要为sql问号设置值
            pst.setInt(1,0);
            pst.setString(2,"测试名字");
            pst.setInt(3,25);
            pst.setString(4,"昆明");

            //4.执行SQL并返回结果 受影响行数
            int i = pst.executeUpdate();
            System.out.println("受影响行数 i = " + i);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closes(conn,pst,null);
        }

    }

    //作业  删除根据id删除    修改 根据id修改

    //根据id查询单个学生
    @Test
    public void selectById(){
        try {
            conn =  JDBCUtils.getConn();
            String sql = "select * from students where id = ?";
             pst = conn.prepareStatement(sql);
            pst.setInt(1,27);

            //结果集是一个表  光标指在数据上一行
             rs = pst.executeQuery();

            //让光标下移动一行 有值返回true  无值返回false
            if(rs.next()){//next 下移  返回值
                //取值
                Student student = new Student();
                //通过列名
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                student.setAddress(rs.getString("address"));

                //通过索引
                System.out.println("student = " + student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closes(conn,pst,rs);
        }


    }


    //查询全部
    @Test
    public void selectByAll(){
        try {
            conn =  JDBCUtils.getConn();
            String sql ="select * from students";
             pst = conn.prepareStatement(sql);
             rs = pst.executeQuery();

            List<Student> list = new ArrayList<>();
            Student student = null;
            //此时结果集有多行数据 需要不断下移遍历数据
            while(rs.next()){

                //取值
               student = new Student();
                //索引
                student.setId(rs.getInt(1));
                student.setName(rs.getString(2));
                student.setAge(rs.getInt(3));
                student.setAddress(rs.getString(4));

                //将对象存储在集合中
                list.add(student);
            }

            System.out.println("list = " + list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.closes(conn,pst,rs);
        }
    }


}
